﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SharePoint;
using FieldUpdater.Classes;
using Oracle = System.Data.OracleClient;
using System.Xml;
using System.Data;

namespace FieldUpdater.SharePointRoot.Template.Layouts
{
    public partial class GetListData : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            bool wasOutputWriten = false;

            try
            {
                var listID = Request["List"];
                var parentField = Request["PF"];
                var lookupID = Request["ID"];

                ListDAL listDAL = new ListDAL(new Guid(listID));

                // Making sure to handle only lookup fields
                if (!(listDAL.List.Fields[parentField] is SPFieldLookup ||
                      listDAL.List.Fields[parentField] is SPFieldChoice ||
                      listDAL.List.Fields[parentField] is SPFieldText))
                {
                    Response.End();
                    return;
                }

                var definitions = listDAL.GetDefinitions();
                var currentAssoc = from field in definitions.Associations
                                   where field.ParentFieldName == parentField &&
                                   (field.ConnectionType == ConnectionTypes.SharePointLookup || field.ConnectionType == ConnectionTypes.Oracle)
                                   select field;

                OracleConnection connectionSettings = null;
                if (definitions.OracleConnections.ContainsKey(parentField))
                    connectionSettings = definitions.OracleConnections[parentField];

                if (currentAssoc.Count() > 0)
                {
                    XmlDocument doc = new XmlDocument();
                    doc.LoadXml("<ItemData />");

                    if (currentAssoc.First().ConnectionType == ConnectionTypes.SharePointLookup)
                    {
                        // Get the lookup field
                        var lookupField = (SPFieldLookup)listDAL.List.Fields[parentField];

                        using (SPWeb lookupWeb = SPContext.Current.Site.OpenWeb(lookupField.LookupWebId))
                        {
                            var list = lookupWeb.Lists[new Guid(lookupField.LookupList)];
                            int lookupIDValue;
                            SPListItem item = null;
                            SPListItemCollection items = null;
                            if (int.TryParse(lookupID, out lookupIDValue))
                                item = list.GetItemById(lookupIDValue);
                            else
                                items = list.GetItems(new SPQuery
                                {
                                    Query = string.Format(
                                    "<Where><Eq><FieldRef Name=\"{0}\" /><Value Type=\"Text\">{1}</Value></Eq></Where>",
                                    lookupField.LookupField,
                                    lookupID)
                                });

                            if (items != null && items.Count > 0)
                                item = items[0];

                            if (item != null)
                                foreach (var association in currentAssoc)
                                {
                                    var meta = doc.CreateElement("Meta");
                                    meta.SetAttribute("FieldName", association.ChildFieldName);

                                    meta.SetAttribute("Value", GetValue(item, association.ConnectingFieldName));
                                    doc.DocumentElement.AppendChild(meta);
                                }
                        }
                    }
                    else if (currentAssoc.First().ConnectionType == ConnectionTypes.Oracle)
                    {
                        using (var _DBConnection = new Oracle.OracleConnection(connectionSettings.ConnectionString))
                        {
                            var columns = (from strVals in connectionSettings.TargetColumns.Split(';')
                                           select strVals.Split('=')[1]).Combine(',');
                            var strQuery = string.Format(
                                OracleConnection.TableSelect,
                                columns,
                                connectionSettings.TableName,
                                connectionSettings.SourceColumn,
                                lookupID);
                            var com = new Oracle.OracleCommand(strQuery, _DBConnection);

                            var da = new Oracle.OracleDataAdapter(com);
                            DataTable dt = new DataTable();
                            da.Fill(dt);

                            if (dt.Rows.Count > 0)
                            {
                                foreach (var association in currentAssoc)
                                {
                                    var meta = doc.CreateElement("Meta");
                                    meta.SetAttribute("FieldName", association.ChildFieldName);

                                    meta.SetAttribute("Value", GetDBValue(dt, association.ConnectingFieldName));
                                    doc.DocumentElement.AppendChild(meta);
                                }
                            }
                        }
                    }

                    Response.Write(doc.OuterXml);
                    wasOutputWriten = true;
                    Response.End();
                }
            }
            catch (Exception) { }
            finally
            {
                if (!wasOutputWriten)
                {
                    Response.Write("<ItemData />");
                    Response.End();
                }
            }
        }

        private string GetDBValue(DataTable dt, string fieldName)
        {
            if (dt.Rows[0][fieldName] == null)
                return string.Empty;
            return dt.Rows[0][fieldName].ToString();
        }

        private string GetValue(SPListItem item, string fieldName)
        {
            if (item == null || item[fieldName] == null)
                return string.Empty;
            return item[fieldName].ToString();
        }
    }
}